Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data
Imports DataAccesGenerales
Imports DataAcces

Public Class DataAccesSolMultiple
	Private Const consTimeOut As Integer = 0 

    ''' <summary>
    ''' Funcion para Insertar un regristro en la tabla SolMultiple
    ''' </summary>
	Public Function SolMultiple_InsertarModificar(pnidDocumento As Long, ptipo_Maniobra As String, pestado As String, particulo As Long, pfecha_Solicitud As String, ppuerto_Destino As Long, ppuerto_Descarga As Long, pbuque As Long, ppatente As String, prectificado As String, pfolio_Entrada As Long, pfolio_Programacion As Long, pfecha_Entrada As String, pfecha_Rectificacion As String, pfecha_Programacion As String, pfeta As String, pidRecinto_Desde As Long, pidRecinto_Hasta As Long, pvid As Long, pid_Mcia As String, ppedimento As String, pregimen As String, pbooking As String, pregistro As String, pconso_Desconso As String, pdc_Emp_Aduanal As String, pemp_Aduanal As String, pcliente As String, prfc As String, pdireccion As String, pciudad As String, pcp As String, plinea_Trans As String, pchofer As String, pplacas As String, pno_Economico As String, pno_Licencia As String, pfecha_Actualizacion As String, prfc_Responsable As String, pnombre_Responsable As String, prepresentante As String, pprocesado_Sip As Long, pprogramado_Calt As Long, prfc_Transportista As String) As Byte
        Dim pTransaccion As Byte = 0
        Dim mensaje As String = String.Empty
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.SolMultipleInsertarModificar"
          oraCommandDocumentos.Parameters.Add("pnidDocumento", pnidDocumento)
          oraCommandDocumentos.Parameters.Add("ptipo_Maniobra", ptipo_Maniobra)
          oraCommandDocumentos.Parameters.Add("pestado", pestado)
          oraCommandDocumentos.Parameters.Add("particulo", particulo)
          oraCommandDocumentos.Parameters.Add("pfecha_Solicitud", pfecha_Solicitud)
          oraCommandDocumentos.Parameters.Add("ppuerto_Destino", ppuerto_Destino)
          oraCommandDocumentos.Parameters.Add("ppuerto_Descarga", ppuerto_Descarga)
          oraCommandDocumentos.Parameters.Add("pbuque", pbuque)
          oraCommandDocumentos.Parameters.Add("ppatente", ppatente)
          oraCommandDocumentos.Parameters.Add("prectificado", prectificado)
          oraCommandDocumentos.Parameters.Add("pfolio_Entrada", pfolio_Entrada)
          oraCommandDocumentos.Parameters.Add("pfolio_Programacion", pfolio_Programacion)
          oraCommandDocumentos.Parameters.Add("pfecha_Entrada", pfecha_Entrada)
          oraCommandDocumentos.Parameters.Add("pfecha_Rectificacion", pfecha_Rectificacion)
          oraCommandDocumentos.Parameters.Add("pfecha_Programacion", pfecha_Programacion)
          oraCommandDocumentos.Parameters.Add("pfeta", pfeta)
          oraCommandDocumentos.Parameters.Add("pidRecinto_Desde", pidRecinto_Desde)
          oraCommandDocumentos.Parameters.Add("pidRecinto_Hasta", pidRecinto_Hasta)
          oraCommandDocumentos.Parameters.Add("pvid", pvid)
          oraCommandDocumentos.Parameters.Add("pid_Mcia", pid_Mcia)
          oraCommandDocumentos.Parameters.Add("ppedimento", ppedimento)
          oraCommandDocumentos.Parameters.Add("pregimen", pregimen)
          oraCommandDocumentos.Parameters.Add("pbooking", pbooking)
          oraCommandDocumentos.Parameters.Add("pregistro", pregistro)
          oraCommandDocumentos.Parameters.Add("pconso_Desconso", pconso_Desconso)
          oraCommandDocumentos.Parameters.Add("pdc_Emp_Aduanal", pdc_Emp_Aduanal)
          oraCommandDocumentos.Parameters.Add("pemp_Aduanal", pemp_Aduanal)
          oraCommandDocumentos.Parameters.Add("pcliente", pcliente)
          oraCommandDocumentos.Parameters.Add("prfc", prfc)
          oraCommandDocumentos.Parameters.Add("pdireccion", pdireccion)
          oraCommandDocumentos.Parameters.Add("pciudad", pciudad)
          oraCommandDocumentos.Parameters.Add("pcp", pcp)
          oraCommandDocumentos.Parameters.Add("plinea_Trans", plinea_Trans)
          oraCommandDocumentos.Parameters.Add("pchofer", pchofer)
          oraCommandDocumentos.Parameters.Add("pplacas", pplacas)
          oraCommandDocumentos.Parameters.Add("pno_Economico", pno_Economico)
          oraCommandDocumentos.Parameters.Add("pno_Licencia", pno_Licencia)
          oraCommandDocumentos.Parameters.Add("pfecha_Actualizacion", pfecha_Actualizacion)
          oraCommandDocumentos.Parameters.Add("prfc_Responsable", prfc_Responsable)
          oraCommandDocumentos.Parameters.Add("pnombre_Responsable", pnombre_Responsable)
          oraCommandDocumentos.Parameters.Add("prepresentante", prepresentante)
          oraCommandDocumentos.Parameters.Add("pprocesado_Sip", pprocesado_Sip)
          oraCommandDocumentos.Parameters.Add("pprogramado_Calt", pprogramado_Calt)
          oraCommandDocumentos.Parameters.Add("prfc_Transportista", prfc_Transportista)

                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

                    oraConnection.Open()
                    oraCommandDocumentos.ExecuteNonQuery()
                    pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
                    mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return pTransaccion
    End Function
  
    ''' <summary>
    ''' Funcion para consultar un registro de la tabla SolMultiple
    ''' </summary>
    ''' <param name="pNidDocumento">numero de documento</param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
   Public Function SolMultiple_Consultar(pNidDocumento As Long) As DataTable
        Dim dtResultado As New DataTable("SolMultipleConsulta")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.SolMultipleConsultar"
                    oraCommandDocumentos.Parameters.Add("pNidDocumento", pNidDocumento)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function
  
    ''' <summary>
    ''' Funcion para generar la cadena XML Chilkat del documento de la tabla SolMultiple
    ''' </summary>
    ''' <param name="nidDocumento"> numero del documento</param>
    ''' <returns>Chilkat.Xml</returns>
    ''' <remarks></remarks>
   Public Function SolMultiple_CadenaOriginalXMLChilkat(nidDocumento As Long) As Chilkat.Xml
        Dim detalleDataSet As New DataSet("DatosSolMultiple")
        Try
      detalleDataSet = SolMultiple_DetalleXML(nidDocumento)
            'En esta parte van las tablas obtenidas en la consulta anterior
            'separadas dependiendo del reporte a crear
            detalleDataSet.Tables(0).TableName = "Informacion del Tramite"
            detalleDataSet.Tables(1).TableName = "Datos SolMultiple"
            detalleDataSet.Tables(2).TableName = "Archivos Anexos"
            detalleDataSet.Tables(3).TableName = "Incidencias"

            Dim xml As New Chilkat.Xml
            xml.LoadXml(detalleDataSet.GetXml())
            Return xml 'detalleDataSet.GetXml()
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    ''' <summary>
    ''' Funcion para consultar el detalle del documento de la tabla SolMultiple
    ''' </summary>
    ''' <param name="pNidDocumento">numero del documento</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
	Public Function SolMultiple_DetalleXML(pNidDocumento As Long) As DataSet
        Dim dsDetalle As New DataSet("SolMultipleDetalle")
        Dim pTransaccion As Byte = 0
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.SolMultipleDetalleXML"
                    oraCommandDocumentos.Parameters.Add("pNidDocumento", pNidDocumento)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pEncabezado", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pDatos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pAnexos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pIncidencias", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    'pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
                    oraDataAdapter.Fill(dsDetalle)
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dsDetalle
    End Function
  
	Public Function SolMultiple_DatosBase() As DataSet
        Dim dtResultado As New DataSet("SolMultiple_DatosBase")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.SolMultipleDatosBase"
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function

    ''' <summary>
    ''' Funcion para generar un combo de la tabla SolMultiple
    ''' </summary>
    ''' <param name="pNombre">nombre a buscar en la tabla</param>
    ''' <returns>DataTable</returns>
    ''' <remarks>si el nombre es "" retornara todos los registros</remarks>
	Public Function SolMultiple_Combo(pNombre As String) As DataTable
        Dim dtResultado As New DataTable("SolMultipleCombo")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.SolMultipleCombo"
                    oraCommandDocumentos.Parameters.Add("pNombre", pNombre)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function

End Class
